package com.example.Cairodio;

import java.text.DateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Vector;

import com.example.entities.Place;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

/**
 * @author Shokry
 * 
 *         This class does all the interactions with the database, other than
 *         creating the database which happens in DatabaseHelper class.
 * 
 */
public class CairodioDbAdapter {

	private Context context;
	private SQLiteDatabase database;
	private DatabaseHelper dbHelper;


	static final String placesTable = "Places";
	static final String colPlaceID = "_id";
	static final String colName = "name";
	static final String colLat = "lat";
	static final String colLong = "long";

	static final String audioClipsTable = "AudioClips";
	static final String colAudioClipID = "_id";
	static final String colURL = "URL";
	static final String colForeign = "place_id";

	static final String commentsTable = "Comments";
	static final String colCommentID = "_id";
	static final String colComment = "Comment";
	static final String colDate = "Date";
	static final String colForeignKey = "place_id";

	static int placeID = 0;
	static int audioClipID = 0;
	static int commentID = 0;
	static int NEAR_BY_METERS = 3000;

	// Constructor
	public CairodioDbAdapter(Context context) {
		this.context = context;
	}

	// Create a new Database Helper
	public CairodioDbAdapter open() throws SQLException {
		dbHelper = new DatabaseHelper(context);
		database = dbHelper.getWritableDatabase();
		// insertAllPlaces();
		return this;
	}

	public void close() {
		dbHelper.close();
	}
/**
 * @author 
 *		Monica Farah (eng.monica.farah@gmail.com)
 * 
 * @param Version
 * @param PlacesNames
 * @param PlacesLat
 * @param PlacesLon
 * @param PlacesUrls
 * @param Comments
 * 
 * The method takes as an input all the places on the server with all the details
 *  and synchronize it with the local DB
 *
 */
	public void SynchronizePlaces(double Version,String[] PlacesNames, double[] PlacesLat,
			double[] PlacesLon, String[] PlacesUrls, String[] Comments) {

		// Checking of the DB versions are not matching the local DB will be updated
		if(database.getVersion()!=Version){
		
			database.delete(placesTable,null,null);
			database.delete(audioClipsTable,null,null);
			 
			
			
		for (int i = 0; i < PlacesNames.length; i++) {
			String[] urls;
			
			if(PlacesUrls[i].equalsIgnoreCase("[]")){
				insertPlaceWizNoClips(PlacesNames[i], PlacesLat[i], PlacesLon[i]);
			}
			else{
				PlacesUrls[i] = PlacesUrls[i].replace("[", "");
				PlacesUrls[i] = PlacesUrls[i].replace("]", "");
			PlacesUrls[i] = PlacesUrls[i].replaceAll("u'", "");
			PlacesUrls[i] = PlacesUrls[i].replaceAll("u'", "");
			PlacesUrls[i] = PlacesUrls[i].replaceAll("'", "");
			urls = PlacesUrls[i].split(", ");
			
				insertPlace(PlacesNames[i], PlacesLat[i], PlacesLon[i], urls);}
		}			
		 database.setVersion((int)Version);	
		}

		
	}
	
	/**
	 * @author 
	 *		Monica Farah (eng.monica.farah@gmail.com)
	 * 
	 * @param name
	 * @param lat
	 * @param lon
	 *
	 * The method is used to insert a tourstic place even if no audios are provided for it 
	 */
	
	private void insertPlaceWizNoClips(String name, double lat, double lon) {
		// TODO Auto-generated method stub

		// Delete if already exists
		deletePlaceWithItsClips(name);


		ContentValues cv = new ContentValues();
		cv.put(colPlaceID, ++placeID);
		cv.put(colName, name);
		cv.put(colLat, lat);
		cv.put(colLong, lon);
		database.insert(placesTable, null, cv);
	}

	/**
	 * @author 
	 *		Monica Farah (eng.monica.farah@gmail.com)
	 * 
	 * @param Comments
	 * @param Places
	 * @param Dates
	 * 
	 * The method takes as an input all the comments from the server
	 *  and update the comments table on the local DB 
	 *
	 */
	public void SynchronizeComments(String[] Comments,String[] Places, String[] Dates) {

		database.delete(commentsTable,null,null);			
		for (int i = 0; i < Comments.length; i++) {
			
			int pID=getID(Places[i]);
				insertComment2(Comments[i], pID, Dates[i]);
				
		}
		
		
	}

	/**
	 * @author 
	 *		Monica Farah (eng.monica.farah@gmail.com)
	 * 
	 * @param comment
	 * @param pID
	 * @param date
	 *
	 * inserting a comment to the DB
	 */
	public void insertComment2(String comment, int pID, String date){

		ContentValues cv = new ContentValues();
//		cv.put(colCommentID, ++commentID);
		cv.put(colComment, comment);
		cv.put(colForeignKey, pID);	
		
		  
		  
		cv.put(colDate, date);;
		database.insert(commentsTable, null, cv);


		Log.e("DATABASE", ">>>>>>>>> Comment Inserted " + comment + date);
		
	}

	/**
	 * @author Shokry
	 * 
	 *         Insert a place along with its audio clips in an array of Strings
	 *         This method deletes any duplicate insertion
	 * 
	 * @param name
	 * @param lat
	 * @param lon
	 * @param urls
	 */
	private void insertPlace(String name, double lat, double lon, String[] urls) {
		// TODO Auto-generated method stub

		// Delete if already exists
		deletePlaceWithItsClips(name);

		// TOURISTIC PLACE AND ITS AUDIO CLIP(S)

		ContentValues cv = new ContentValues();
		cv.put(colPlaceID, ++placeID);
		cv.put(colName, name);
		cv.put(colLat, lat);
		cv.put(colLong, lon);
		database.insert(placesTable, null, cv);

		// insert audio clips
		for (int i = 0; i < urls.length; i++) {
			cv = new ContentValues();
			cv.put(colAudioClipID, ++audioClipID);
			cv.put(colURL, urls[i]);
			cv.put(colForeign, placeID);
			database.insert(audioClipsTable, null, cv);
		}
		//

		// Log.e("DATABASE", ">>>>>>>>>>>>>>>>>>>>> Inserted " + name);
	}

	/**
	 * @author Shokry
	 * 
	 *         Return the id of a Place given its name
	 * 
	 * @param name
	 * @return
	 */
	public int getID(String name) {
		// TODO Auto-generated method stub
		Cursor c = database.rawQuery("SELECT " + colPlaceID + " FROM "
				+ placesTable + " WHERE " + colName + "=?",
				new String[] { name });

		if (c.getCount() > 0) {
			c.moveToFirst();
			return c.getInt(c.getColumnIndex(colPlaceID));
		} else
			return -1;
	}
	
	/**
	 * @author Shokry
	 * 
	 * returns the name of a place given its id
	 * 
	 * @param id
	 * @return
	 */
	public String getPlaceName(int id) {
		// TODO Auto-generated method stub
		Cursor c = database.rawQuery("SELECT " + colName + " FROM "
				+ placesTable + " WHERE " + colPlaceID + "=?",
				new String[] { id+"" });

		if (c.getCount() > 0) {
			c.moveToFirst();
			return c.getString(c.getColumnIndex(colName));
		} else
			return "no name available";
	}
	
	/**
	 * @author Shokry
	 * 
	 * returns the Place object given its id
	 * 
	 * @param id
	 * @return
	 */
	public Place getPlace(int id) {
		Cursor c = database.rawQuery("SELECT " + colName + "," + colLat + ","
				+ colLong + " FROM " + placesTable + " WHERE " + colPlaceID
				+ "=?", new String[] { id + "" });
		;

		Place place;

		c.moveToFirst();
		if (!c.isAfterLast()) {

			double placeLat = c.getDouble(c.getColumnIndex(colLat));
			double placeLong = c.getDouble(c.getColumnIndex(colLong));

			String name = c.getString(c.getColumnIndex(colName));
			place = new Place(name, placeLat, placeLong);
			
		} else {
			place = null;
		}
		return place;

	}

	/**
	 * @author Shokry
	 * 
	 *         Return cursor for all places available in the DB
	 * 
	 * @return
	 */
	public Cursor getAllPlaces() {
		// TODO Auto-generated method stub
		return database.rawQuery("SELECT * FROM " + placesTable, null);
	}

	/**
	 * @author Shokry
	 * 
	 *         Delete place and its related clips
	 * 
	 * @param name
	 */
	public void deletePlaceWithItsClips(String name) {
		// TODO Auto-generated method stub
		int id = getID(name);
		if (id != -1) {
			database.delete(placesTable, colName + "= '" + name + "'", null);
			database.delete(audioClipsTable, colForeign + "=" + id, null);

			Log.e("DATABASE", ">>>>>>>>>>>>>>>>>>>>> Deleted " + name);
		}
	}

	/**
	 * @author Shokry
	 * 
	 *         method to return all audio clips for a certain place It returns a
	 *         vector containing all URLs
	 * 
	 * @param placeID
	 * @return
	 */
	public Vector<String> getAudioClips(int placeID) {

		Vector<String> urls = new Vector<String>();

		// SQLiteDatabase db = this.getReadableDatabase();
		Cursor c = database.rawQuery("SELECT " + colURL + " FROM "
				+ audioClipsTable + " WHERE " + colForeign + "=?",
				new String[] { placeID + "" });

		c.moveToFirst();
		while (!c.isAfterLast()) {

			urls.add(c.getString(c.getColumnIndex(colURL)));
			c.moveToNext();
		}

		return urls;

	}

	/**
	 * @author Shokry
	 * 
	 *         method to return all nearby by places given the current
	 *         GEO-Location
	 * 
	 * @param currentLat
	 * @param currentLong
	 * @return
	 */
	public Vector<String> getNearByPlacesName(double currentLat,
			double currentLong) {

		Vector<String> names = new Vector<String>();

		// SQLiteDatabase db = this.getReadableDatabase();
		Cursor c = database.rawQuery("SELECT " + colName + "," + colLat + ","
				+ colLong + " FROM " + placesTable, null);

		c.moveToFirst();
		while (!c.isAfterLast()) {

			double placeLat = c.getDouble(c.getColumnIndex(colLat));
			double placeLong = c.getDouble(c.getColumnIndex(colLong));

			double distanceInMeters = distFrom(currentLat, currentLong,
					placeLat, placeLong);

			if (distanceInMeters < NEAR_BY_METERS)
				names.add(c.getString(c.getColumnIndex(colName)));

			c.moveToNext();
		}

		return names;

	}

	/**
	 * @author Shokry
	 * 
	 * Get a list of places (object) that are nearby currentLat and currentLong
	 * within the area of the given radius
	 * 
	 * @param currentLat
	 * @param currentLong
	 * @param radius
	 * @return
	 */
	public ArrayList<Place> getNearByPlacesWithRadius(double currentLat,
			double currentLong, int radius) {

		ArrayList<Place> nearbyPlaces = new ArrayList<Place>();
		Cursor c = database.rawQuery("SELECT " + colName + "," + colLat + ","
				+ colLong + " FROM " + placesTable, null);

		c.moveToFirst();
		while (!c.isAfterLast()) {

			double placeLat = c.getDouble(c.getColumnIndex(colLat));
			double placeLong = c.getDouble(c.getColumnIndex(colLong));
			double distanceInMeters = distFrom(currentLat, currentLong,
					placeLat, placeLong);

			if (distanceInMeters < radius) {

				String name = c.getString(c.getColumnIndex(colName));
				Place place = new Place(name, placeLat, placeLong);
				nearbyPlaces.add(place);
			}

			c.moveToNext();
		}

		return nearbyPlaces;

	}

	/**
	 * @author Shokry
	 * 
	 *         Method to get the distance between 2 GEO-coordinates in meters
	 * 
	 * @param lat1
	 * @param lng1
	 * @param lat2
	 * @param lng2
	 * @return
	 */
	public double distFrom(double lat1, double lng1, double lat2, double lng2) {
		double earthRadius = 3958.75;
		double dLat = Math.toRadians(lat2 - lat1);
		double dLng = Math.toRadians(lng2 - lng1);
		double a = Math.sin(dLat / 2) * Math.sin(dLat / 2)
				+ Math.cos(Math.toRadians(lat1))
				* Math.cos(Math.toRadians(lat2)) * Math.sin(dLng / 2)
				* Math.sin(dLng / 2);
		double c = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1 - a));
		double dist = earthRadius * c;

		int meterConversion = 1609;

		return new Float(dist * meterConversion).floatValue();
	}
	
	/**
	 * @author Mariam
	 * 
	 * This method inserts a comment in the comments table in the local database
	 * 
	 * @param comment
	 * @param pID
	 */
	
	public void insertComment(String comment, int pID){

		ContentValues cv = new ContentValues();
		cv.put(colComment, comment);
		cv.put(colForeignKey, pID);	
		Date now = new Date();
		cv.put(colDate, DateFormat.getDateTimeInstance(
	            DateFormat.MEDIUM, DateFormat.SHORT).format(now));
		database.insert(commentsTable, null, cv);


		Log.e("DATABASE", ">>>>>>>>> Comment Inserted " + comment + DateFormat.getDateTimeInstance(
	            DateFormat.MEDIUM, DateFormat.SHORT).format(now) );
		
	}
	
	/**
	 * @author Mariam
	 * 
	 * This method gets all the comments for a specific place
	 * 
	 * @param pID
	 * @return
	 */
	public Vector<String> getComments(int pID) {

		Vector<String> comments = new Vector<String>();

		Cursor c = database.rawQuery("SELECT " + colComment + " FROM "
				+ commentsTable + " WHERE " + colForeignKey + "=?",
				new String[] { pID + "" });

		c.moveToFirst();
		while (!c.isAfterLast()) {

			comments.add(c.getString(c.getColumnIndex(colComment)));
			c.moveToNext();
		}

		return comments;

	}
	
	/**
	 * @author Mariam
	 * 
	 * This method deleted all comments of a specific place
	 * 
	 * @param id
	 */

	public void deletePlaceComments(int id) {
		// TODO Auto-generated method stub
		if (id != -1) {
			database.delete(commentsTable, colForeignKey + "=" + id, null);

			Log.e("DATABASE", ">>>>>>>>>>>>>>>>>>>>> Deleted comments");
		}
	}
	
	/**
	 * @author Mariam
	 * 
	 * This method gets all the dates of the comments of a specific place
	 * 
	 * @param pID
	 * @return
	 */

	public Vector<String> getAllCommentsDates(int pID) {

		Vector<String> dates = new Vector<String>();

		Cursor c = database.rawQuery("SELECT " + colDate + " FROM "
				+ commentsTable + " WHERE " + colForeignKey + "=?",
				new String[] { pID + "" });

		c.moveToFirst();
		while (!c.isAfterLast()) {

			dates.add(c.getString(c.getColumnIndex(colDate)));
			c.moveToNext();
		}

		return dates;
	}
	
	/**
	 * @author Mariam
	 * 
	 * This method returns a cursor of comments of a specific place
	 * to get from it all desired columns.
	 * 
	 * @param pID
	 * @return
	 */

	public Cursor getAllComments(int pID) {

		Cursor c = database.rawQuery("SELECT * FROM " + commentsTable
				+ " WHERE " + colForeignKey + "=?", new String[] { pID + "" });
		return c;
	}
}
